SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create PROCEDURE [dbo].[SP_TTAMAR_OCPR_D01]        
/*-------------------------------------------------------*/        
/*---Empresa              : OFISIS S.A.                --*/        
/*---Cliente              : OFISIS S.A.                --*/        
/*---Sistema              : Tesoreria                  --*/        
/*---M¸dulo               : Empresa                    --*/        
/*---Programa             : Anulaci©n O/C con Facturas --*/        
/*---Script               : SP_TTAMAR_OCPR_D01.sql     --*/        
/*---Nombre SP            : SP_TTAMAR_OCPR_D01         --*/        
/*---Desarrollado por     : Denis Villacr's P.         --*/        
/*---Fecha Creaci¸n       : 10/07/2000                 --*/        
/*---Base Datos           : Microsoft Sql Server       --*/        
/*---Versi¸n              : 7.0                        --*/        
/*---Invoca a SP          :                            --*/        
/*-------------------------------------------------------*/        
        
/*-------------------------------------------------------*/      
/*---Modificado 1 por     : Maribel Julca Rodriguez    --*/      
/*---Fecha Modificaci©n   : 21/04/2005                 --*/       
/*---Detalle Modificaci©n : ingreso de los campos CO_USUA_CREA, CO_USUA_MODI  --*/      
/*-------------------------------------------------------*/      
/*---Modificado 2 por     : Miguel Carrasco    --*/      
/*---Fecha Modificaci©n   : 29/01/2011                 --*/       
/*---Detalle Modificaci©n : Considerar descontar saldo al momento de anular cuando el
ST_CIER_DOCU = 'S' y no lo pagado --*/      

/*-------------------------------------------------------*/      
        
/*-------------------------------------------------------*/        
/*---Comentarios                                       --*/        
/*---                                                  --*/        
/*---                                                  --*/        
/*-------------------------------------------------------*/        
        
/*-------------------------------------------------------*/        
/*---Drop Proc SP_TTAMAR_OCPR_D01                      --*/        
/*---Grant all on SP_TTAMAR_OCPR_D01 to public         --*/         
/*---SP_TTAMAR_OCPR_D01 '01', 'OFISIS', '00000007', '', '029'            --*/        
/*-------------------------------------------------------*/        
 @ISCO_EMPR        TD_VC_002,         
 @ISCO_USUA_MODI   TD_VC_008_USUA,        
 @ISNU_AMAR        TD_VC_008,        
 @ISCO_PROV        TD_VC_020,        
 @ISCO_TIPO_OPER   TD_VC_003,      
 @ISCO_UNID_CONC   TD_VC_003,      
 @IDFE_AMAR    TD_DT_001  
 As  
DECLARE @CSCO_ENTI  TD_VC_020,         
  @CSTI_DOCU_DEST TD_VC_003,        
  @CSNU_DOCU_DEST TD_VC_020,          
  @CNIM_PAGA_DEST TD_NU_016_004,         
  @CNIM_PAGA_ORIG TD_NU_016_004,         
  @VNIM_TOTA_DOCU TD_NU_016_004,         
  @CSTI_DOCU_ORIG TD_VC_003,         
  @CSNU_DOCU_ORIG TD_VC_020,          
  @VNIM_PAGA      TD_NU_016_004,
  @CNIM_SALD_DEST TD_NU_016_004,
  @CSST_CIER_DOCU TD_VC_001       
        
  /***  LIBERA DOCUMENTOS  DESTINOS  ***/        
        
 BEGIN TRAN SP_TTAMAR_OPAN_D01        
        


     DECLARE CU_TTAMAR_NCPA_D01 CURSOR         
     FOR        
     SELECT CO_ENTI, CO_TIDO_DEST , NU_DOCU_DEST , IM_PAGO_DEST,IM_SALD_DEST,ST_CIER_DOCU        
     FROM   TDAMAR_OCPR        
     WHERE  CO_EMPR = @ISCO_EMPR         
     AND    CO_UNID_CONC = @ISCO_UNID_CONC      
     AND    NU_AMAR  = @ISNU_AMAR         
     AND    FE_AMAR  = @IDFE_AMAR         
     AND    CO_TIDO_ORIG is NULL         
     AND    NU_DOCU_ORIG is NULL            
        
     OPEN CU_TTAMAR_NCPA_D01        
     FETCH  CU_TTAMAR_NCPA_D01 INTO @CSCO_ENTI, @CSTI_DOCU_DEST , @CSNU_DOCU_DEST , 
     @CNIM_PAGA_DEST,@CNIM_SALD_DEST,@CSST_CIER_DOCU         
        
     While (@@FETCH_STATUS = 0)        
       Begin        
  If (@@FETCH_STATUS < 0)        
       Begin        
      RAISERROR 20001 'FIN DE ARCHIVO'        
      CLOSE CU_TTAMAR_NCPA_D01        
      DEALLOCATE CU_TTAMAR_NCPA_D01        
      Return                    
           End        
        

 IF @CSST_CIER_DOCU = 'S'
  BEGIN
    SELECT @CNIM_PAGA_DEST = @CNIM_SALD_DEST --- MIGUEL
  END

 Update TMDOCU_PROV         
 Set IM_PAGA = Isnull(IM_PAGA,0) - Isnull(@CNIM_PAGA_DEST,0) , CO_ESTA_DOCU = 'ACT',        
            CO_USUA_MODI = @ISCO_USUA_MODI, FE_USUA_MODI = GETDATE()        
 Where CO_EMPR      = @ISCO_EMPR         
        And   CO_PROV      = @CSCO_ENTI         
        And   CO_TIPO_DOCU = @CSTI_DOCU_DEST         
        And   NU_DOCU_PROV = @CSNU_DOCU_DEST        
        
 If @@error <> 0        
           Begin        
      CLOSE CU_TTAMAR_NCPA_D01        
      DEALLOCATE CU_TTAMAR_NCPA_D01        
             Rollback Tran         
             Return        
           End        
        
 FETCH  CU_TTAMAR_NCPA_D01 INTO @CSCO_ENTI, @CSTI_DOCU_DEST , @CSNU_DOCU_DEST , @CNIM_PAGA_DEST,
        @CNIM_SALD_DEST,@CSST_CIER_DOCU                 
     END         
       
     CLOSE CU_TTAMAR_NCPA_D01        
     DEALLOCATE CU_TTAMAR_NCPA_D01        
        
     /***  LIBERA DOCUMENTOS  ORIGENES  ***/        
        
     DECLARE CU_TTAMAR_NCPA_D01 CURSOR         
     FOR        
     SELECT CO_TIDO_ORIG , NU_DOCU_ORIG, IM_PAGO_ORIG        
     FROM   TDAMAR_OCPR        
     WHERE  CO_EMPR = @ISCO_EMPR         
     AND    CO_UNID_CONC = @ISCO_UNID_CONC      
     AND    NU_AMAR  = @ISNU_AMAR         
     AND    FE_AMAR  = @IDFE_AMAR         
     AND    CO_TIDO_DEST is  NULL         
     AND    NU_DOCU_DEST is  NULL            
        
     OPEN CU_TTAMAR_NCPA_D01        
     FETCH  CU_TTAMAR_NCPA_D01 INTO @CSTI_DOCU_ORIG , @CSNU_DOCU_ORIG, @CNIM_PAGA_ORIG        
        
     While (@@FETCH_STATUS = 0)        
       Begin        
  If (@@FETCH_STATUS < 0)        
        Begin        
       RAISERROR 20001 'FIN DE ARCHIVO'        
       CLOSE CU_TTAMAR_NCPA_D01        
       DEALLOCATE CU_TTAMAR_NCPA_D01        
       RETURN        
            End        
        
   SELECT @VNIM_TOTA_DOCU  = 0, @VNIM_PAGA = 0        
        
         SELECT @VNIM_TOTA_DOCU  = ISNULL(IM_TOTA,0), @VNIM_PAGA = ISNULL(IM_PAGA,0)        
         FROM TMDOCU_PROV         
         WHERE CO_EMPR = @ISCO_EMPR         
         AND CO_PROV = @ISCO_PROV         
         AND CO_TIPO_DOCU = @CSTI_DOCU_ORIG         
         AND NU_DOCU_PROV = @CSNU_DOCU_ORIG        
        
    IF @VNIM_TOTA_DOCU != @VNIM_PAGA        
       BEGIN               
        UPDATE TMDOCU_PROV         
        SET  CO_USUA_MODI = @ISCO_USUA_MODI, FE_USUA_MODI = GETDATE()        
        WHERE CO_EMPR = @ISCO_EMPR         
              AND   CO_PROV = @ISCO_PROV         
              AND   CO_TIPO_DOCU = @CSTI_DOCU_ORIG         
              AND   NU_DOCU_PROV = @CSNU_DOCU_ORIG        
        
          If @@error <> 0        
                 Begin        
            CLOSE CU_TTAMAR_NCPA_D01        
            DEALLOCATE CU_TTAMAR_NCPA_D01        
                   rollback tran         
                   return        
                 End        
  END         
        
       FETCH  CU_TTAMAR_NCPA_D01 INTO @CSTI_DOCU_ORIG , @CSNU_DOCU_ORIG, @CNIM_PAGA_ORIG
     END         
        
    CLOSE CU_TTAMAR_NCPA_D01        
    DEALLOCATE CU_TTAMAR_NCPA_D01        
        
        
 Insert into  TMREGI_ELIM (CO_EMPR, CO_TABL_ORIG, DE_TABL_ORIG,        
           CO_CLAV_TAOR, CO_USUA_CREA,FE_USUA_CREA, CO_USUA_MODI, FE_USUA_MODI )        
    select @ISCO_EMPR, 89, 'DETALLE APLICACI.N O/C A DOCUMENTOS PROVEEDOR',        
        RTRIM( @ISCO_EMPR )+ REPLICATE('#', 2-LEN(RTRIM(@ISCO_EMPR)))+         
        RTRIM( @ISNU_AMAR )+ REPLICATE('#', 8-LEN(RTRIM(@ISNU_AMAR)))+        
        RTRIM( NU_SECU )+ REPLICATE('#', 1-LEN(RTRIM(NU_SECU))), @ISCO_USUA_MODI,GETDATE(), @ISCO_USUA_MODI, GETDATE()        
   From  TDAMAR_OCPR        
 Where CO_EMPR = @ISCO_EMPR         
     AND    CO_UNID_CONC = @ISCO_UNID_CONC      
     AND    NU_AMAR  = @ISNU_AMAR         
     AND    FE_AMAR  = @IDFE_AMAR         
        
   If @@error <> 0        
      Begin        
        Rollback tran        
        Return        
      End            
        
    Delete TDAMAR_OCPR        
     Where CO_EMPR = @ISCO_EMPR         
     AND    CO_UNID_CONC = @ISCO_UNID_CONC      
     AND    NU_AMAR  = @ISNU_AMAR         
     AND    FE_AMAR  = @IDFE_AMAR         
        
    If @@error <> 0        
       Begin        
         Rollback tran        
         Return        
       End                
        
        
 Insert into  TMREGI_ELIM (CO_EMPR, CO_TABL_ORIG, DE_TABL_ORIG,        
           CO_CLAV_TAOR, CO_USUA_CREA,FE_USUA_CREA, CO_USUA_MODI, FE_USUA_MODI)         
    select @ISCO_EMPR, 27, 'CABECERA APLICACI.N O/C A DOCUMENTOS PROVEEDOR',        
        RTRIM( @ISCO_EMPR )+ REPLICATE('#', 2-LEN(RTRIM(@ISCO_EMPR)))+         
        RTRIM( @ISNU_AMAR )+ REPLICATE('#', 8-LEN(RTRIM(@ISNU_AMAR))), @ISCO_USUA_MODI,GETDATE(), @ISCO_USUA_MODI, GETDATE()        
 From   TCAMAR_OCPR        
 Where  CO_EMPR = @ISCO_EMPR         
     AND    CO_UNID_CONC = @ISCO_UNID_CONC      
     AND    NU_AMAR  = @ISNU_AMAR         
     AND    FE_AMAR  = @IDFE_AMAR         
        
   If @@error <> 0        
      Begin        
        Rollback tran        
        Return        
      End            
        
  Delete TCAMAR_OCPR        
     Where CO_EMPR = @ISCO_EMPR         
     AND    CO_UNID_CONC = @ISCO_UNID_CONC      
     AND    NU_AMAR  = @ISNU_AMAR         
     AND    FE_AMAR  = @IDFE_AMAR         
         
    If @@error <> 0        
       Begin        
         Rollback tran        
         Return        
       End             
        
        
 COMMIT TRAN         
        
        
/*------------------------ Fin ------------------------------*/  
  

GO
